﻿CREATE PROCEDURE [dbo].[proc_Orders_GetList_Project]
	(
		@CompanyId int,
		@SText nvarchar(50),
		@StartIndex int,
		@EndIndex int,
		@StartDate nvarchar(50),
		@EndDate nvarchar (50),
		@ProductId int
	)
AS
	begin
	;WITH list As(Select ROW_NUMBER() OVER (ORDER BY O.ProjectFlag)AS Row,
		O.Id,
		O.OId,
		O.OrderId,
		O.CustomerId,
		O.CustomerName,
		O.ContactPerson,
		O.ContactPhone,
		O.UserName,
		EName,
		isnull((select top 1 fra.tradedate from Finance_RunningAccount fra where fra.orderid=O.OId),'1900-01-01')  as TradeDate,
		O.Description,
		O.Aggregateamount,
		IsNull((select sum(fr.InCome) From Finance_RunningAccount fr where fr.OrderId=O.OId),'0') as Proceeds,
		IsNull((Select Sum(CostPrice) From Project Where OrderId=O.OId),'0') As ChengBen,
		O.CreateDate,
		O.CompanyId,
		O.FinanceFlag,
		O.ProjectFlag,
		O.PaymentTypeId,
		O.BaiDuAccount,
		(select p.title from product p where p.id=MainProjectId) as MainProject
	From Orders O 
	Where (@CompanyId=0 Or CompanyId=@CompanyId) And FinanceFlag=1 And (@SText is null Or CustomerName like '%'+ @SText +'%' Or EName like '%'+ @SText +'%' Or BaiDuAccount like '%'+@SText+'%')
		  and (Convert(varchar(10),FixtureDate,120)>=@StartDate and Convert(varchar(10),FixtureDate,120)<=@EndDate)
          and (@ProductId=0 or MainProjectId=@ProductId) 
	)
	Select * From list Where Row between @StartIndex and @EndIndex
	
	end
	RETURN
